from win32com.client import Dispatch
import win32com.client
import pymssql
import time

if __name__ == "__main__":
      xlApp = None
      xlBook = None
      
      try:
            xlApp = win32com.client.Dispatch('Excel.Application')
            xlBook = xlApp.Workbooks.Open('D:\\Agent Lead\\Alpha Roster 20111021.xls')

            xlSheet = xlBook.ActiveSheet
            count = 0
            querySql = None
            
            conn = pymssql.connect(host='10.20.120.100', user='sa',
                                   password='Adm1n1ns)1', database='ALODDB')
            cur = conn.cursor()

            cur.execute('truncate table AlphaRoster')
            conn.commit()
            
            for i in range(1, xlSheet.Rows.Count):
                  if (xlSheet.Cells(i, 1).Value == None):
                        break
                  if (i != 1):
                        sql = 'insert into AlphaRoster'\
                              + '(division, region, lastname, firstname, agtcode, market, territory)'\
                              + 'values(\'{0}\', \'{1}\', \'{2}\', \'{3}\', \'{4}\', \'{5}\', \'{6}\')'
                        
                        querySql = sql.format(xlSheet.Cells(i, 1).Value, xlSheet.Cells(i, 2).Value,\
                                         xlSheet.Cells(i, 3).Value.replace('\'', '\'\''), xlSheet.Cells(i, 4).Value,\
                                         xlSheet.Cells(i, 5).Value, xlSheet.Cells(i, 14).Value, xlSheet.Cells(i, 15).Value)
                        try:
                              cur.execute(querySql)
                              conn.commit()
                              count =  count + 1
                        except Exception as e:
                              print 'Exception:' + e
                              print querySql

            cur.execute('exec dbo.sp_Import_Agent')
            conn.commit()

            print '{0} records are inserted'.format(count)
            
      except Exception as e:
            print 'Exception:' + e
      finally:
            if (conn != None):
                  conn.close()
                  
            if (xlBook != None):
                  xlBook.Close(SaveChanges=0)

            if (xlApp != None):
                  xlApp.Quit()
            del xlApp
